package com.asiainfo.operationdb;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import com.asiainfo.datamodel.Coach;
import com.asiainfo.datamodel.CourseShedule;
import com.asiainfo.datamodel.SubscribeCourse;
import com.asiainfo.datamodel.User;
import com.asiainfo.util.JDBCUtil;

public class AddData {
	public Connection conn = null;
	public PreparedStatement ps = null;
	public Statement stmt = null;
	public ResultSet rs = null;

	/**
	 * 增加会员卡，办卡业务
	 * 
	 * @param userName
	 *            ：用户名/会员名
	 * 
	 * @param cardTab
	 *            :卡类型，分为年卡和次卡
	 * 
	 * @param times
	 *            :次数，年卡为-1，次卡为使用次数
	 * 
	 */
	public void addCard(String userName, String cardTab, int times) {
		CheckData checkData = new CheckData();
		int userID = checkData.getID(userName, "select * from user_info");
		String sql = "insert into card_info(user_id, card_tab,card_count,card_starttime,card_endtime,card_state) values(?,?,?,?,?,?)";
		SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd");
		Calendar calendar = Calendar.getInstance();
		String startTime = sFormat.format(calendar.getTime());
		if (times == -1)
			calendar.add(Calendar.YEAR, +1);
		else
			calendar.add(Calendar.YEAR, +3);
		String endTime = sFormat.format(calendar.getTime());
		java.sql.Date startDate = java.sql.Date.valueOf(startTime);
		java.sql.Date endDate = java.sql.Date.valueOf(endTime);
		conn = JDBCUtil.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userID);
			ps.setString(2, cardTab);
			ps.setInt(3, times);
			ps.setDate(4, startDate);
			ps.setDate(5, endDate);
			ps.setInt(6, 1);
			ps.executeUpdate();
			System.out.println("A new " + cardTab + " added to " + userName
					+ "(" + userID + ")");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, stmt, ps, rs);
		}
	}

	/**
	 * 增加会员user
	 * 
	 * @param user
	 *            ：user类，包含姓名，地址，职业等数据类
	 * 
	 */
	public void addMember(User user) {
		String sql = "insert into user_info(user_name, user_address, user_career) values(?,?,?)";
		conn = JDBCUtil.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUserName());
			ps.setString(2, user.getUserAddress());
			ps.setString(3, user.getUserCareer());
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, stmt, ps, rs);
		}
		CheckData checkData = new CheckData();
		int userID = checkData.getID(user.getUserName(),
				"select * from user_info");
		System.out.println(user.getUserName() + "'s member ID is " + userID);

	}

	/**
	 * 增加课程
	 * 
	 * @param courseName
	 */
	public void addCourse(String courseName) {
		String sql = "insert into course_info(course_name) values(?)";
		conn = JDBCUtil.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, courseName);
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, stmt, ps, rs);
		}
		System.out.println("OK,课程【" + courseName + "】添加成功！");
	}

	/**
	 * 增加教练
	 * 
	 * @param coach
	 */
	public void addCoach(Coach coach) {
		String sql = "insert into coach_info(coach_name, coach_family, coach_age,coach_specialty) values(?,?,?,?)";
		conn = JDBCUtil.getConnection();
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, coach.getCoachName());
			ps.setString(2, coach.getCoachFamily());
			ps.setInt(3, coach.getCoachAge());
			ps.setString(4, coach.getCoachSpecialty());

			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, stmt, ps, rs);
		}
		System.out.println("OK,教练【" + coach.getCoachName() + "】添加成功！");
	}

	/**
	 * 排课安排
	 * 
	 * @param courseShedule
	 */
	public void addCourseShedule(CourseShedule courseShedule) {
		String sql = "insert into schedule_course(schedule_id, course_id, coach_id,course_name,coach_name) values(?,?,?,?,?)";
		conn = JDBCUtil.getConnection();
		int courseID = courseShedule.getCourseID();
		int coachID = courseShedule.getCoachID();
		if (courseID != 0 && coachID != 0) {
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, courseShedule.getScheduleIDd());
				ps.setInt(2, courseShedule.getCourseID());
				ps.setInt(3, courseShedule.getCoachID());
				ps.setString(4, courseShedule.getCourseName());
				ps.setString(5, courseShedule.getCoachName());

				ps.executeUpdate();

			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				JDBCUtil.close(conn, stmt, ps, rs);
			}
			System.out.println("OK,排课成功！");
		} else
			System.out.println("查询不到相关课程或教练，请重新输入！");

	}

	/**
	 * 预订课称
	 */
	public void addSubscribeCourse(SubscribeCourse subscribeCourse) {
		CheckData checkData = new CheckData();
		String sheduleID = subscribeCourse.getScheduleID();
		String userName = subscribeCourse.getUserName();
		int userID = checkData.getID(userName, "select * from user_info");
		boolean result = checkData.isSheduleID(sheduleID,
				"select * from schedule_course");
		if (result == false)
			System.out.println("查询不到此课程，请重新订阅课程！");
		else if (userID == 0) {
			System.out.println("查询不到该学员，请重新输入！");
		} else {
			String sql = "insert into subscribe_info(schedule_id, user_name) values(?,?)";
			conn = JDBCUtil.getConnection();

			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, sheduleID);
				ps.setString(2, userName);
				ps.executeUpdate();

			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				JDBCUtil.close(conn, stmt, ps, rs);
			}

			System.out.println("OK,"
					+ userName
					+ "预定成功，请于"
					+ sheduleID
					+ "准时到剑瑜伽馆,修炼"
					+ checkData.getCoachName(sheduleID,
							"select * from schedule_course")
					+ "的"
					+ checkData.getCourseName(sheduleID,
							"select * from schedule_course"));

		}
	}

}
